import numpy as np
import pandas as pd
from openpyxl import load_workbook

from common import load_sheet, column_names

# 删除物流单号为空、停发和疫情停发的行
def preprocess(df, column_name, values_to_drop=[]):
    values_to_drop.append(np.nan)
    df_subset = df[df[column_name].isin(values_to_drop)]
    df.drop(df_subset.index, inplace=True)

# 计算销售金额
def calc_sales(df):
    df[column_names["sale_amnt"]] = \
        df[column_names["chan_pric"]] * \
        df[column_names["comm_quat"]] + \
        df[column_names["chan_fare"]]
    return df

def save_excel(xl_path, df, sheet_name):
    workbook = load_workbook(xl_path)
    # 
    if sheet_name in workbook:
        # Delete existing worksheet
        del workbook[sheet_name]
    # 
    worksheet = workbook.create_sheet(sheet_name)
    # 
    worksheet.append(list(df.columns))
    for row in df.values:
        worksheet.append(list(row))
    
    workbook.save(xl_path)

def sales_detail_process(wb_path, sheet_name):

    # 加载sheet
    df = load_sheet(wb_path, sheet_name)
    # 进行预处理
    preprocess(df, '物流单号', values_to_drop = ['停发', '疫情停发'])
    # 
    df = calc_sales(df)
    # 保存至excel
    save_excel(wb_path, df, sheet_name)


if __name__ == '__main__':

    # 
    wb_path = 'data/01-03销售日报.xlsx'
    sheet_name = '订单明细'
    # 
    sales_detail_process(wb_path, sheet_name)
